<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <title> &middot; </title>
        <meta name="description" content="hg_job 作者
chrisx
日期
2021-03-11
内容
HGDB hg_job功能，定时任务功能可以减轻数据库操作人员的手工重复工作，提高工作的效率，对于数据定时处理工作带来了极大的便利。
 [toc]
参考管理手册
hg_job配置(功能逻辑) 参数 cat $PGDATA/postgresql.conf
shared_preload_libraries #后台进程hg_job_scheduler需要预先加载库文件 #------------------------------------------------------------------------------ # HGDB JOB #------------------------------------------------------------------------------ hg.job_database = &#39;highgo&#39; #后台进程（hg_job_scheduler）要连接的数据库，默认highgo hg.job_queue_processes = 0 #作业进程并发数，默认为0，0表示不启动。需要配置大于0 hg.job_queue_interval = 3 #后台工作进程 hg_job_scheduler 扫描 job 的频率,单位为秒， #取值范围为 1..3600，默认为 3 秒，建议与时间间隔成倍数关系。 hg.job_log_path = &#39;hg_job&#39; #日志保存路径，默认路径为：$PGDATA/hg_job/，需要先在$PGDATA 目录下创建 hg_job 目录。 启用
alter system set shared_preload_libraries=&#39;hg_job&#39;; --默认已经添加  alter system set hg.job_database = &#39;highgo&#39;; alter system set hg.job_queue_processes = 4; alter system set hg.">
        <meta name="HandheldFriendly" content="True">
        <meta name="MobileOptimized" content="320">
        <meta name="generator" content="Hugo 0.81.0" />
        <meta name="robots" content="index,follow">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        
        <link rel="stylesheet" href="https://chrisxian.gitee.io/dist/site.css">
        <link rel="stylesheet" href="https://chrisxian.gitee.io/dist/syntax.css">
        <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,400,600,700,300&subset=latin,cyrillic-ext,latin-ext,cyrillic">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous">
        
        
        
        
        

    </head>
    <body>
        

        <div id="wrapper">
            <header class="site-header">
                <div class="container">
                    <div class="site-title-wrapper">
                        
                            <h1 class="site-title">
                                <a href="https://chrisxian.gitee.io/">chrisx</a>
                            </h1>
                        
                        
                        
                        
                        
                        
                        
                        
                        
                    </div>

                    <ul class="site-nav">
                        

                    </ul>
                </div>
            </header>

            <div id="container">


<div class="container">
    <article class="post-container">
        <header class="post-header">
    <h1 class="post-title"></h1>
    
</header>

        <div class="post-content clearfix">
    

    <h1 id="hg_job">hg_job</h1>
<p><strong>作者</strong></p>
<p>chrisx</p>
<p><strong>日期</strong></p>
<p>2021-03-11</p>
<p><strong>内容</strong></p>
<p>HGDB hg_job功能，定时任务功能可以减轻数据库操作人员的手工重复工作，提高工作的效率，对于数据定时处理工作带来了极大的便利。</p>
<hr>
<p>[toc]</p>
<p>参考<strong>管理手册</strong></p>
<h2 id="hg_job配置功能逻辑">hg_job配置(功能逻辑)</h2>
<h3 id="参数">参数</h3>
<p>cat $PGDATA/postgresql.conf</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-bash" data-lang="bash">shared_preload_libraries <span style="color:#75715e">#后台进程hg_job_scheduler需要预先加载库文件</span>
<span style="color:#75715e">#------------------------------------------------------------------------------</span>
<span style="color:#75715e"># HGDB JOB</span>
<span style="color:#75715e">#------------------------------------------------------------------------------</span>

hg.job_database <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;highgo&#39;</span> <span style="color:#75715e">#后台进程（hg_job_scheduler）要连接的数据库，默认highgo</span>
hg.job_queue_processes <span style="color:#f92672">=</span> <span style="color:#ae81ff">0</span> <span style="color:#75715e">#作业进程并发数，默认为0，0表示不启动。需要配置大于0</span>
hg.job_queue_interval <span style="color:#f92672">=</span> <span style="color:#ae81ff">3</span> <span style="color:#75715e">#后台工作进程 hg_job_scheduler 扫描 job 的频率,单位为秒，</span>
<span style="color:#75715e">#取值范围为 1..3600，默认为 3 秒，建议与时间间隔成倍数关系。</span>
hg.job_log_path <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;hg_job&#39;</span> <span style="color:#75715e">#日志保存路径，默认路径为：$PGDATA/hg_job/，需要先在$PGDATA 目录下创建 hg_job 目录。</span>

</code></pre></div><p>启用</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">system</span> <span style="color:#66d9ef">set</span> shared_preload_libraries<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;hg_job&#39;</span>; <span style="color:#75715e">--默认已经添加
</span><span style="color:#75715e"></span>
<span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">system</span> <span style="color:#66d9ef">set</span> hg.job_database <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;highgo&#39;</span>;
<span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">system</span> <span style="color:#66d9ef">set</span> hg.job_queue_processes <span style="color:#f92672">=</span> <span style="color:#ae81ff">4</span>;
<span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">system</span> <span style="color:#66d9ef">set</span> hg.job_queue_interval <span style="color:#f92672">=</span> <span style="color:#ae81ff">9</span>;
<span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">system</span> <span style="color:#66d9ef">set</span> hg.job_log_path <span style="color:#f92672">=</span> <span style="color:#e6db74">&#39;hg_job&#39;</span>;

</code></pre></div><blockquote>
<p>注意默认有hg_job扩展（\dx）</p>
</blockquote>
<h3 id="监控进程">监控进程</h3>
<ol>
<li>后台工作进程
配置参数 hg.job_queue_processes=0，默认不启用定时任务功能，也就没有后台工作者进程。hg.job_queue_processes&gt;0 就会启用定时任务。后台会产生hg_job_scheduler工作进程。</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-shell" data-lang="shell"><span style="color:#f92672">[</span>hgdb565@db data<span style="color:#f92672">]</span>$ ps -ef |grep hg_job
hgdb565   <span style="color:#ae81ff">2406</span>  <span style="color:#ae81ff">2394</span>  <span style="color:#ae81ff">0</span> 15:35 ?        00:00:00 postgres: bgworker: hg_job_scheduler

</code></pre></div><ol start="2">
<li>作业队列进程
后台工作进程轮询 job 创建作业队列进程，执行 job，执行完释放作业队列进程。定时任务支持并发，作业队列进程以 libpq 方式创建连接，hg.job_queue_processes 应设置合理的取值范围（0..max_connectios/2）。</li>
</ol>
<h3 id="hg_job元数据表">hg_job元数据表</h3>
<p>为了实现对 HighGo Database 定时任务更好的管理，HG_JOB 定义两张元数据表，记录任务内容，运行情况等信息。</p>
<p>:warning: 注意 hg_job 元数据所在的模式必须是hg_job 定时任务管理的数据库上，否则定时任务管理不能正常进行。</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">highgo<span style="color:#f92672">=#</span> <span style="color:#960050;background-color:#1e0010">\</span>d hgjob.job; <span style="color:#75715e">--Hgjob.job 元数据表是记录定时任务属性的表
</span><span style="color:#75715e"></span>highgo<span style="color:#f92672">=#</span> <span style="color:#960050;background-color:#1e0010">\</span>d hgjob.jobrunning; <span style="color:#75715e">--hgjob.jobrunning 元数据表是记录正在执行的定时任务的表
</span></code></pre></div><h2 id="hg_job启用">HG_JOB启用</h2>
<p>有两种方式启用定时任务功能：</p>
<ol>
<li>
<p>配置文件中，修改参数 hg.job_queue_processes&gt;0，重启数据库，自动启动后台工作进程，并创建 hg_job 扩展，启用定时任务。</p>
</li>
<li>
<p>使用 ALTER SYSTEM 命令，修改 hg.job_queue_processes&gt;0，重新 reload 或重启数据库，手动创建 hg_job 扩展，调用 job_launch()函数启动后台工作进程，启用定时任务。</p>
</li>
</ol>
<h2 id="hg_job-操作函数">HG_JOB 操作函数</h2>
<p>hg_job 中对定时任务的管理都是通过函数来实现，包括创建，修改等操作。这些函数也都是在 hg_job 模式下。</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">highgo<span style="color:#f92672">=#</span> <span style="color:#66d9ef">select</span> p.proname,n.nspname <span style="color:#66d9ef">from</span> pg_proc p,pg_namespace n <span style="color:#66d9ef">where</span> proname <span style="color:#66d9ef">like</span> <span style="color:#e6db74">&#39;job_%&#39;</span> <span style="color:#66d9ef">and</span> p.pronamespace<span style="color:#f92672">=</span>n.oid;
   proname    <span style="color:#f92672">|</span> nspname
<span style="color:#75715e">--------------+---------
</span><span style="color:#75715e"></span> job_change   <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--更改用户设定的任务参数
</span><span style="color:#75715e"></span> job_create   <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--提交一个新任务，系统指定一个任务号
</span><span style="color:#75715e"></span> job_delete   <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--从队列中删除一个已存在的任务
</span><span style="color:#75715e"></span> job_interval <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--更改任务运行的时间间隔
</span><span style="color:#75715e"></span> job_launch   <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--启动后台工作者进程
</span><span style="color:#75715e"></span> job_nextrun  <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--更改任务下一次运行时间
</span><span style="color:#75715e"></span> job_run      <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--立即执行任务
</span><span style="color:#75715e"></span> job_start    <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--将停止的 job 重新启用
</span><span style="color:#75715e"></span> job_stop     <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--将任务停止，不让其重复运行
</span><span style="color:#75715e"></span> job_what     <span style="color:#f92672">|</span> hgjob    <span style="color:#75715e">--更改 PSQL 任务定义
</span><span style="color:#75715e"></span>(<span style="color:#ae81ff">10</span> <span style="color:#66d9ef">rows</span>)

</code></pre></div><h3 id="创建定时任务job_create">创建定时任务（job_create）</h3>
<ol>
<li>间隔时间若不定义，即为空，则本次 job 仅执行一次然后停止。</li>
<li>job 执行前，通过间隔时间预先计算下一次执行时间，再用计算结果代替下次执行时间的值，然后去执行 job。</li>
<li>job 创建时应检查创建语句是否有效，若无效（表不存在、语法错误、job 运行时间为错误时间等）则不插入数据（即创建失败），并根据相应的错误内容进行输出提示。</li>
<li>创建定时任务时可指定 job 的运行开始时间，若不指定则默认当前时间立即执行。</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> test_t (info varchar,time <span style="color:#66d9ef">timestamp</span>);


highgo<span style="color:#f92672">=#</span> <span style="color:#66d9ef">select</span> hgjob.job_create(<span style="color:#960050;background-color:#1e0010">$$</span><span style="color:#66d9ef">insert</span> <span style="color:#66d9ef">into</span> <span style="color:#66d9ef">public</span>.test_t (info, time) <span style="color:#66d9ef">values</span> (<span style="color:#e6db74">&#39;jobid:1&#39;</span>,now());<span style="color:#960050;background-color:#1e0010">$$</span>, <span style="color:#960050;background-color:#1e0010">$$</span>now() <span style="color:#f92672">+</span> interval <span style="color:#e6db74">&#39;5 m&#39;</span><span style="color:#960050;background-color:#1e0010">$$</span>, now());
     job_create
<span style="color:#75715e">--------------------
</span><span style="color:#75715e"></span> Job <span style="color:#66d9ef">create</span> success
(<span style="color:#ae81ff">1</span> <span style="color:#66d9ef">row</span>)

</code></pre></div><h3 id="修改定时任务job_change">修改定时任务（job_change）</h3>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> hgjob.job_change(<span style="color:#ae81ff">1</span>, <span style="color:#960050;background-color:#1e0010">$$</span><span style="color:#66d9ef">insert</span> <span style="color:#66d9ef">into</span> <span style="color:#66d9ef">public</span>.test_t (info, time) <span style="color:#66d9ef">values</span> (<span style="color:#e6db74">&#39;1111&#39;</span>,now());<span style="color:#960050;background-color:#1e0010">$$</span>,<span style="color:#960050;background-color:#1e0010">$$</span>now() <span style="color:#f92672">+</span> interval <span style="color:#e6db74">&#39;1 m&#39;</span><span style="color:#960050;background-color:#1e0010">$$</span> );

</code></pre></div><h3 id="停止定时任务job_stop">停止定时任务（job_stop）</h3>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> hgjob.job_stop(<span style="color:#ae81ff">1</span>);

</code></pre></div><h3 id="启动定时任务job_start">启动定时任务（job_start）</h3>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> hgjob.job_start(<span style="color:#ae81ff">3</span>, <span style="color:#e6db74">&#39;2018-05-30 00:00:00&#39;</span>); <span style="color:#75715e">--指定任务 id、下次执行时间，启动任务
</span><span style="color:#75715e"></span>
</code></pre></div><h3 id="删除定时任务job_delete">删除定时任务（job_delete）</h3>
<h2 id="hg_job-任务监控">HG_JOB 任务监控</h2>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> jobid,jobenabled,jobnextrun,jobstartrun,jobuser,jobcount <span style="color:#66d9ef">from</span> hgjob.job;
<span style="color:#66d9ef">select</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">from</span> hgjob.jobrunning;

<span style="color:#66d9ef">select</span> hgjob.job_stop(<span style="color:#ae81ff">1</span>);
</code></pre></div>
</div>
    </article>
</div>

            </div>
        </div>

        <footer class="footer">
            <div class="container">
                <div class="site-title-wrapper">
                    <h1 class="site-title">
                        <a href="https://chrisxian.gitee.io/">chrisx</a>
                    </h1>
                    <a class="button-square button-jump-top js-jump-top" href="#" aria-label="Back to Top">
                        <i class="fa fa-angle-up" aria-hidden="true"></i>
                    </a>
                </div>

                <p class="footer-copyright">
                    <span>&copy; 0001 / Powered by <a href="https://gohugo.io/">Hugo</a></span>
                </p>
                <p class="footer-copyright">
                    <span><a href="https://github.com/roryg/ghostwriter">Ghostwriter theme</a> By <a href="http://jollygoodthemes.com">JollyGoodThemes</a></span>
                    <span>/ <a href="https://github.com/jbub/ghostwriter">Ported</a> to Hugo By <a href="https://github.com/jbub">jbub</a></span>
                </p>
            </div>
        </footer>

        <script src="https://chrisxian.gitee.io/js/jquery-1.11.3.min.js"></script>
        <script src="https://chrisxian.gitee.io/js/jquery.fitvids.js"></script>
        <script src="https://chrisxian.gitee.io/js/scripts.js"></script>
    </body>
</html>

